import org.apache.spark.sql.{DataFrame, SparkSession}

object SparkSql02 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName(SparkSql02.getClass.getSimpleName)
      .master("local[*]")
      .getOrCreate()

    val students = spark.read
      .option("Header", "true")
      .option("Delimiter", "|")
      .csv("file:/F:/student.csv")
    students.show()
    students.createOrReplaceTempView("students")
    val stuSumScore = spark.sql(
      """
        			  |select s.id , s.name, s.age, s.gender, sum(s.score) sumScore, avg(s.score) avgScore
        			  |from students as s
        			  |group by s.name, s.id, s.age, s.gender
        			  |""".stripMargin)
    stuSumScore.show()
    stuSumScore.createOrReplaceTempView("stuSumScore")

    //func1(spark, stuSumScore)
    //func2(spark, stuSumScore)
    //func3(spark, stuSumScore)
    //func4(spark, students)
    //func5(spark, stuSumScore)
    //func6(spark, stuSumScore)
    //func7(spark, students)
    func8(spark, stuSumScore)
    spark.stop()
  }

  /**
    * 1. 一共有多少人参加考试？
    *    1.1 一共有多少个小于20岁的人参加考试？
    *    1.2 一共有多少个等于20岁的人参加考试？
    *    1.3 一共有多少个大于20岁的人参加考试？
    */
  def func1(spark: SparkSession, stuSumScore: DataFrame) = {
    val stuCount = stuSumScore.count()
    println(s"一共有${stuCount}人参加考试")
    val less = stuSumScore.where("age<20").count()
    println(s"一共有${less}个小于20岁的人参加考试")
    val eq = stuSumScore.where("age==20").count()
    println(s"一共有${eq}个等于20岁的人参加考试")
    val greater = stuSumScore.where("age>20").count()
    println(s"一共有${greater}个大于20岁的人参加考试")
  }

  /**
    * 2. 一共有多个男生参加考试？
    *    2.1 一共有多少个女生参加考试？
    */
  def func2(spark: SparkSession, stuSumScore: DataFrame) = {
    val boyCount = stuSumScore.where("gender == '男' ").count()
    println(s"一共有${boyCount}个男生参加考试")
    val girlCount = stuSumScore.where("gender == '女' ").count()
    println(s"一共有${girlCount}个女生参加考试")
  }

  /**
    * 3. 12班有多少人参加考试？
    *    3.1 13班有多少人参加考试？
    */
  def func3(spark: SparkSession, stuSumScore: DataFrame) = {
    val Num12 = stuSumScore.where("id == 12").count()
    println(s"12班有${Num12}人参加考试")
    val Num13 = stuSumScore.where("id == 13").count()
    println(s"12班有${Num13}人参加考试")
  }

  /**
    * 4. 语文科目的平均成绩是多少？
    *    4.1 数学科目的平均成绩是多少？
    *    4.2 英语科目的平均成绩是多少？
    */
  def func4(spark: SparkSession, students: DataFrame) = {
    spark.sql(
      """
        			  |select course, avg(score) avgScore
        			  |from students
        			  |group by course
        			  |""".stripMargin).show()
  }

  /**
    * 5. 单个人平均成绩是多少？
    */
  def func5(spark: SparkSession, stuSumScore: DataFrame) = {
    stuSumScore.select("name", "avgScore").show()
  }

  /**
    * 6. 12班平均成绩是多少？
    *    6.1 12班男生平均总成绩是多少？
    *    6.2 12班女生平均总成绩是多少？
    *    6.3 同理求13班相关成绩
    */
  def func6(spark: SparkSession, stuSumScore: DataFrame) = {
    println("-----------------班级平均成绩-----------------")
    spark.sql(
      """
        			  |select classId, avg(sumScore) as  classAvgScore
        			  |from stuSumScore
        			  |group by id
        			  |""".stripMargin).show()

    println("-----------------12班男女生平均成绩-----------------")
    spark.sql(
      """
        			  |select classId, gender, avg(sumScore) as  genderAvgScore
        			  |from stuSumScore
        			  |where id = 12
        			  |group by gender, id
        			  |""".stripMargin).show()

    println("-----------------13班男女生平均成绩-----------------")
    spark.sql(
      """
        			  |select classId, gender, avg(sumScore) as  genderAvgScore
        			  |from stuSumScore
        			  |where id = 13
        			  |group by gender, id
        			  |""".stripMargin).show()
  }

  /**
    * 7. 全校语文成绩最高分是多少？
    *    7.1 12班语文成绩最低分是多少？
    *    7.2 13班数学最高成绩是多少？
    */
  def func7(spark: SparkSession, students: DataFrame) = {
    spark.sql(
      """
        			  |select id, course, max(score) maxScore, min(score) minScore
        			  |from students
        			  |group by id, course
        			  |""".stripMargin).show()
  }

  /**
    * 8. 总成绩大于150分的12班的女生有几个？
    */
  def func8(spark: SparkSession, stuSumScore: DataFrame) = {
    val count = stuSumScore.where("id == 12 and gender == '女' and sumScore > 150").count()
    println(s"总成绩大于150分的12班的女生有${count}个")
  }

}
